﻿using System.Collections.Generic;
using ExamPlat.Models;
using ExamPlat.DAL;
using System.Data;
using ExamPlat.ViewModels;
using System;

namespace ExamPlat.Repositories
{
    public class ExamQuesItemRepository : IExamQuesItemRepository
    {
        /// <summary>
        /// 增加考题问题
        /// </summary>
        /// <param name="model">考题问题信息</param>
        /// <returns></returns>
        public int AddExamQuesItem(ExamQuesItem model, List<ExamQuesItemOption> optionList)
        {
            try
            {
                string strType = "";
                if (model.ExamQuesItemType == 1)
                {
                    strType = "SingleScore";
                }
                if (model.ExamQuesItemType == 2)
                {
                    strType = "MultipleScore";
                }
                if (model.ExamQuesItemType == 3)
                {
                    strType = "JudgeScore";
                }
                string sql = string.Format(@"insert into ExamQuesItem(ExamQuesID,ExamQuesItemTitle,ExamQuesItemType,ExamQuesItemScore,
                                                                          ExamQuesItemAnswer) values({0},'{1}',{2},(select {3} from ExamQues where ID={0}),'{4}')", 
                                                                          model.ExamQuesID, model.ExamQuesItemTitle, model.ExamQuesItemType, strType,
                                                                          model.ExamQuesItemAnswer);
                sql += ";select @@IDENTITY";
                foreach (var item in optionList)
                {
                    sql += string.Format(";insert into ExamQuesItemOption(ExamQuesItemID,ExamQuesItemOptionTitle,ExamQuesItemOptionNum) values(##myID##,'{0}','{1}')", item.ExamQuesItemOptionTitle, item.ExamQuesItemOptionNum);
                }

                return SqlHelper.ExecuteTransaction(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        /// <summary>
        /// 修改考题问题
        /// </summary>
        /// <param name="model">考题问题信息</param>
        /// <returns></returns>
        public int UpdateExamQuesItem(ExamQuesItem model, List<ExamQuesItemOption> optionList)
        {
            try
            {
                string strType = "";
                if (model.ExamQuesItemType == 1)
                {
                    strType = "SingleScore";
                }
                if (model.ExamQuesItemType == 2)
                {
                    strType = "MultipleScore";
                }
                if (model.ExamQuesItemType == 3)
                {
                    strType = "JudgeScore";
                }
                string sql = string.Format("delete from ExamQuesItemOption where ExamQuesItemID = {0}", model.ID);
                sql += string.Format(";update ExamQuesItem set ExamQuesID={0},ExamQuesItemTitle='{1}',ExamQuesItemType={2},ExamQuesItemAnswer='{3}',ExamQuesItemScore=(select {4} from ExamQues where ID={0})  where ID={5}", model.ExamQuesID, model.ExamQuesItemTitle, model.ExamQuesItemType, model.ExamQuesItemAnswer, strType, model.ID);
                foreach (var item in optionList)
                {
                    sql += string.Format(";insert into ExamQuesItemOption(ExamQuesItemID,ExamQuesItemOptionTitle,ExamQuesItemOptionNum) values({0},'{1}','{2}')", model.ID, item.ExamQuesItemOptionTitle, item.ExamQuesItemOptionNum);
                }
                return SqlHelper.ExecuteTransaction(sql);

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 删除考题问题
        /// </summary>
        /// <param name="id">考题问题ID</param>
        /// <returns></returns>
        public int DelExamQuesItem(int id)
        {
            try
            {
                string sql = string.Format("delete from ExamQuesItemOption where ExamQuesItemID ={0}", id);
                sql += string.Format(";delete from ExamQuesItem where ID = {0}", id);
                return SqlHelper.ExecuteTransaction(sql);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据考题问题ID查询考题信息
        /// </summary>
        /// <param name="id">考题问题ID</param>
        /// <returns></returns>
        public ExamQuesItem GetExamQuesItemByID(int id)
        {
            try
            {
                string sql = string.Format("select A.*,B.ExamQuesTitle from ExamQuesItem A left outer join ExamQues B on A.ExamQuesID = B.ID where A.ID={0}  ", id);
                DataSet ds = SqlHelper.ExecuteQuery(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    ExamQuesItem result = new ExamQuesItem();
                    DataTable dt = ds.Tables[0];
                    result.ID = (int)dt.Rows[0]["ID"];
                    result.ExamQuesID = (int)dt.Rows[0]["ExamQuesID"];
                    result.ExamQuesItemTitle = dt.Rows[0]["ExamQuesItemTitle"].ToString();
                    result.ExamQuesItemType = (int)dt.Rows[0]["ExamQuesItemType"];
                    result.ExamQuesItemScore = (int)dt.Rows[0]["ExamQuesItemScore"];
                    result.ExamQuesItemAnswer = dt.Rows[0]["ExamQuesItemAnswer"].ToString();
                    sql = string.Format("select * from ExamQuesItemOption where ExamQuesItemID = {0}", id);
                    ds = SqlHelper.ExecuteQuery(sql);
                    if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                    {
                        dt = ds.Tables[0];
                        List<ExamQuesItemOption> option = new List<ExamQuesItemOption>();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            ExamQuesItemOption tmpResult = new ExamQuesItemOption();
                            tmpResult.ID = (int)dt.Rows[i]["ID"];
                            tmpResult.ExamQuesItemID = (int)dt.Rows[i]["ExamQuesItemID"];
                            tmpResult.ExamQuesItemOptionTitle = dt.Rows[i]["ExamQuesItemOptionTitle"].ToString();
                            option.Add(tmpResult);
                        }
                        result.OptionList = option;
                    }
                    return result;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 根据条件查询考题信息
        /// </summary>
        /// <param name="examQuesID">考题ID</param>
        /// <param name="examQuesItemTitle">考题问题标题</param>
        /// <param name="ExamQuesItemType">考题问题类型</param>
        /// <returns></returns>
        public IList<ViewExamQuesItem> GetExamQuesItemByCondition(int examQuesID, string examQuesItemTitle, int examQuesItemType)
        {
            try
            {
                string sql = "select A.*,B.ExamQuesTitle from ExamQuesItem A left outer join ExamQues B on A.ExamQuesID = B.ID where 1=1 ";
                if (examQuesID != 0)
                {
                    sql += string.Format(" and A.ExamQuesID = {0}", examQuesID);
                }
                if (!string.IsNullOrWhiteSpace(examQuesItemTitle))
                {
                    sql += string.Format(" and A.ExamQuesItemTitle like '%{0}%'", examQuesItemTitle);
                }
                if (examQuesItemType != 0)
                {
                    sql += string.Format(" and A.ExamQuesItemType = {0}", examQuesItemType);
                }
                DataSet ds = SqlHelper.ExecuteQuery(sql);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    IList<ViewExamQuesItem> result = new List<ViewExamQuesItem>();
                    DataTable dt = ds.Tables[0];
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        ViewExamQuesItem tmpResult = new ViewExamQuesItem();
                        tmpResult.ID = (int)dt.Rows[i]["ID"];
                        tmpResult.ExamQuesID = (int)dt.Rows[i]["ExamQuesID"];
                        tmpResult.ExamQuesItemTitle = dt.Rows[i]["ExamQuesItemTitle"].ToString();
                        tmpResult.ExamQuesItemType = (int)dt.Rows[i]["ExamQuesItemType"];
                        tmpResult.ExamQuesItemScore = (int)dt.Rows[i]["ExamQuesItemScore"];
                        tmpResult.ExamQuesItemAnswer = dt.Rows[i]["ExamQuesItemAnswer"].ToString();
                        tmpResult.ExamQuesTitle = dt.Rows[i]["ExamQuesTitle"].ToString();

                        string  optionSql = string.Format("select * from ExamQuesItemOption where ExamQuesItemID = {0}", tmpResult.ID);
                        DataSet optionDS = SqlHelper.ExecuteQuery(optionSql);
                        if (optionDS != null && optionDS.Tables.Count > 0 && optionDS.Tables[0].Rows.Count > 0)
                        {
                            DataTable optionDt = optionDS.Tables[0];
                            List<ExamQuesItemOption> optionList = new List<ExamQuesItemOption>();
                            for (int j = 0; j < optionDt.Rows.Count; j++)
                            {
                                ExamQuesItemOption optionResult = new ExamQuesItemOption();
                                optionResult.ID = (int)optionDt.Rows[j]["ID"];
                                optionResult.ExamQuesItemID = (int)optionDt.Rows[j]["ExamQuesItemID"];
                                optionResult.ExamQuesItemOptionTitle = optionDt.Rows[j]["ExamQuesItemOptionTitle"].ToString();
                                optionResult.ExamQuesItemOptionNum = optionDt.Rows[j]["ExamQuesItemOptionNum"].ToString();
                                optionList.Add(optionResult);
                            }
                            tmpResult.OptionList = optionList;
                        }
                        result.Add(tmpResult);
                    }
                    return result;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }
}